import sqlite3
from datetime import datetime
import logging
import threading


logger = logging.getLogger(__name__)

class DatabaseManager:
    def __init__(self, db_path, system=None):
        self.db_path = db_path
        self.system = system  # 保留对TradingSystem的引用
        self.local = threading.local()  # 线程本地存储
#        self.conn = None
#        self.cursor = None
        logger.info("数据库管理器初始化完成")
#        self.connect()
#        self.init_database()

    def connect(self):
        """连接到数据库"""
        try:
            # 如果连接已存在，先关闭
            if not hasattr(self.local, "conn") or not self.local.conn:
             self.local.conn = sqlite3.connect(self.db_path)
             self.local.conn.row_factory = sqlite3.Row
            #self.cursor = self.conn.cursor()
            #logger.info("数据库连接成功")
            return self.local.conn
        except Exception as e:
            logger.error(f"数据库连接失败: {e}")
            return None

    def get_cursor(self):
        """获取当前线程的游标"""
        conn = self.connect()
        if not conn:
            return None
        if not hasattr(self.local, "cursor") or not self.local.cursor:
            self.local.cursor = conn.cursor()
        return self.local.cursor

    def close(self):
        """关闭数据库连接"""
        if self.conn:
            self.conn.close()
            self.conn = None

    def init_database(self):
        """初始化数据库表结构"""
        if not self.connect():
            return False
        try:
            # 初始化资金为100万
            self.execute_query("INSERT OR IGNORE INTO Capital (RecordDate, Amount) VALUES (?, ?)",
                               (datetime.now().isoformat(), 1000000))

            logger.info("数据库表结构初始化完成")
            return True
        except Exception as e:
            logger.error(f"数据库初始化失败: {e}")
            return False

    def execute_query(self, query, params=(), fetch=False):
        """执行SQL查询"""
        try:
            cursor = self.get_cursor()
            if not cursor:
                return None

            cursor.execute(query, params)
            self.connect().commit()

            if fetch:
                return [dict(row) for row in cursor.fetchall()]
            return True
        except Exception as e:
            logger.error(f"数据库查询失败: {e}")
            return None

    # 高手管理方法
    def add_master(self, master_name, follow_status=False, added_date=None, notes="", tradeslast=None, sdtj=False):
        """添加高手"""
        if added_date is None:
            added_date = datetime.now().isoformat()
        if tradeslast is None:
            tradeslast = datetime.min.isoformat()
            
        return self.execute_query(
            "INSERT INTO Masters (MasterName, FollowStatus, AddedDate, Notes, TradesLast, SDTJ) VALUES (?, ?, ?, ?, ?, ?)",
            (master_name, int(follow_status), added_date, notes, tradeslast, int(sdtj))
        )
    
    def get_masters(self):
        """获取所有高手"""
        return self.execute_query("SELECT * FROM Masters", fetch=True)
    
    def get_followed_masters(self):
        """获取跟踪中的高手"""
        return self.execute_query("SELECT * FROM Masters WHERE FollowStatus = 1", fetch=True)
    
    def update_master(self, master_id, follow_status=None, notes=None):
        """更新高手信息"""
        updates = []
        params = []
        
        if follow_status is not None:
            updates.append("FollowStatus = ?")
            params.append(int(follow_status))
        if notes is not None:
            updates.append("Notes = ?")
            params.append(notes)
        
        if not updates:
            return False
        
        params.append(master_id)
        query = f"UPDATE Masters SET {', '.join(updates)} WHERE MasterID = ?"
        return self.execute_query(query, params)
    
    def get_master_id_by_name(self, master_name):
        """根据名称获取高手ID"""
        result = self.execute_query(
            "SELECT MasterID FROM Masters WHERE MasterName = ?",
            (master_name,),
            fetch=True
        )
        return result[0]['MasterID'] if result else None

    def get_master_last_update(self, master_id):
        """获取高手的上次更新时间"""
        result = self.execute_query(
            "SELECT TradesLast FROM Masters WHERE MasterID = ?",
            (master_id,),
            fetch=True
        )
        if result and result[0].get('TradesLast'):
            return result[0]['TradesLast']
        return datetime.min.isoformat()

    def update_master_last_update(self, master_id, last_update):
        """更新高手的上次更新时间"""
        return self.execute_query(
            "UPDATE Masters SET TradesLast = ? WHERE MasterID = ?",
            (last_update.isoformat(), master_id)
        )

    def add_trade(self, master_id, stock_code, stock_name, trade_type, trade_price, trade_volume, trade_date,
                  source="东方财富"):
        """添加交易记录并发出信号"""
        try:
            # 执行插入操作
            self.execute_query(
                "INSERT INTO Trades (MasterID, StockCode, StockName, TradeType, TradePrice, TradeVolume, TradeDate, Source) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
                (master_id, stock_code, stock_name, trade_type, trade_price, trade_volume, trade_date.isoformat(),
                 source)
            )

            # 发出交易变更信号
            if self.system:
                self.system.trades_changed.emit()

            return True
        except Exception as e:
            logger.error(f"添加交易记录失败: {e}")
            return False

    def get_trades_by_master(self, master_id):
        """根据高手ID获取交易记录"""
        return self.execute_query(
            "SELECT * FROM Trades WHERE MasterID = ? ORDER BY TradeDate DESC",
            (master_id,),
            fetch=True
        )

    def get_monitor_trades(self, limit=100):
        """获取监控交易记录（跟踪高手的新交易）"""
        query = """
            SELECT t.TradeID, t.TradeDate, m.MasterName, t.StockCode, t.StockName, 
                   t.TradeType, t.TradePrice, t.TradeVolume, t.IsCopied
            FROM Trades t
            JOIN Masters m ON t.MasterID = m.MasterID
            WHERE m.FollowStatus = 1
            ORDER BY t.TradeDate DESC
            LIMIT ?
        """
        return self.execute_query(query, (limit,), fetch=True)

    def get_simulated_trades(self, limit=100):
        """获取跟单信息记录（按时间倒序）"""
        query = """
            SELECT s.SimDate, t.StockCode, t.StockName, t.TradeType, 
                   s.SimPrice, s.SimVolume, s.SimResult, s.ProfitLoss, s.Profitsyje
            FROM SimulatedTrades s
            JOIN Trades t ON s.TradeID = t.TradeID
            ORDER BY s.SimDate DESC
            LIMIT ?
        """
        return self.execute_query(query, (limit,), fetch=True)

    def is_duplicate_trade(self, master_id, trade):
        """检查是否为重复交易记录"""
        # 获取该高手的所有交易记录
        existing_trades = self.get_trades_by_master(master_id)
        # 检查是否有相同股票、相同类型、相同价格的交易
        for existing_trade in existing_trades:
            # 转换时间格式
            if (existing_trade['MasterID'] == master_id and
                existing_trade['StockCode'] == trade['StockCode'] and
                existing_trade['TradeType'] == trade['TradeType'] and
                existing_trade['TradePrice'] == trade['TradePrice'] and
                datetime.fromisoformat(existing_trade['TradeDate']) == trade['TradeDate']
            ):
                return True
        return False

    # 模拟交易方法
    def add_simulated_trade(self, trade_id, sim_price, sim_volume, sim_result, profit_loss, profitsyje):
        """添加模拟交易记录并标记原始交易为已跟单"""
        # 添加模拟交易记录
        result = self.execute_query(
            "INSERT INTO SimulatedTrades (TradeID, SimDate, SimPrice, SimVolume, SimResult, ProfitLoss, Profitsyje) VALUES (?, ?, ?, ?, ?, ?, ?)",
            (trade_id, datetime.now().isoformat(), sim_price, sim_volume, sim_result, profit_loss, profitsyje)
        )
        if self.system:
            self.system.simulated_trades_changed.emit()  # 发出跟单信息变更信号
        
        # 标记原始交易为已跟单
        if result:
            self.execute_query(
                "UPDATE Trades SET IsCopied = 1 WHERE TradeID = ?",
                (trade_id,)
            )
        return result

    # 资金管理方法
    def get_current_capital(self):
        """获取当前资金"""
        result = self.execute_query(
            "SELECT Amount FROM Capital ORDER BY RecordDate DESC LIMIT 1",
            fetch=True
        )
        return result[0]['Amount'] if result else 1000000

    def get_system_setting(self, setting_key):
        """获取系统设置值"""
        query = "SELECT SettingValue FROM SystemSettings WHERE SettingName = ?"
        result = self.execute_query(query, (setting_key,), fetch=True)
        if result:
            return float(result[0]['SettingValue'])
        return 0.0  # 默认值

    def update_capital(self, amount):
        """更新资金"""
        return self.execute_query(
            "INSERT INTO Capital (RecordDate, Amount) VALUES (?, ?)",
            (datetime.now().isoformat(), amount)
        )

    # 绩效评估方法
    def add_performance_report(self, report):
        """添加绩效报告"""
        return self.execute_query(
            "INSERT INTO PerformanceReports (MasterID, PeriodType, StartDate, EndDate, InitialCapital, FinalCapital, ProfitLoss, WinRate, Evaluation, ReportDate) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
            (
                report['MasterID'], report['PeriodType'], report['StartDate'], report['EndDate'],
                report['InitialCapital'], report['FinalCapital'], report['ProfitLoss'],
                report['WinRate'], report['Evaluation'], datetime.now().isoformat()
            )
        )

    def get_performance_reports(self, master_id=None):
        """获取绩效报告"""
        if master_id:
            return self.execute_query(
                "SELECT * FROM PerformanceReports WHERE MasterID = ? ORDER BY EndDate DESC",
                (master_id,),
                fetch=True
            )
        return self.execute_query("SELECT * FROM PerformanceReports ORDER BY EndDate DESC", fetch=True)

    def is_trade_copied(self, trade_id):
        """检查交易是否已跟单"""
        result = self.execute_query(
            "SELECT IsCopied FROM Trades WHERE TradeID = ?",
            (trade_id,),
            fetch=True
        )
        return bool(result[0]['IsCopied']) if result else False

    def get_popular_stocks(self):
        """获取当天超过3位高手买入的热门股票"""
        try:
            today = datetime.now().strftime("%Y-%m-%d")

            # 查询当天买入记录 - 直接从 Trades 表获取
            query = """
                SELECT 
                    t.StockCode,
                    t.StockName,
                    COUNT(DISTINCT t.MasterID) AS buyer_count,
                    json_group_array(
                        json_object(
                            'master_id', m.MasterID,
                            'master_name', m.MasterName,
                            'trade_type', t.TradeType,
                            'trade_price', t.TradePrice,
                            'trade_volume', t.TradeVolume,
                            'trade_date', t.TradeDate
                        )
                    ) AS buyers_json
                FROM Trades t
                JOIN Masters m ON t.MasterID = m.MasterID
                WHERE DATE(t.TradeDate) = ?
                AND t.TradeType = '买入'
                GROUP BY t.StockCode
                HAVING buyer_count >= 3
                ORDER BY buyer_count DESC
            """
            params = (today,)
            result = self.execute_query(query, params, fetch=True)

            if not result:
                return {}

            popular_stocks = {}
            for row in result:
                stock_code = row['StockCode']
                popular_stocks[stock_code] = {
                    'stock_name': row['StockName'],
                    'buyer_count': row['buyer_count'],
                    'buyers': []
                }

                # 解析买家JSON
                buyers = json.loads(row['buyers_json'])
                for buyer in buyers:
                    popular_stocks[stock_code]['buyers'].append({
                        'master_id': buyer['master_id'],
                        'master_name': buyer['master_name'],
                        'trade_type': buyer['trade_type'],
                        'trade_price': buyer['trade_price'],
                        'trade_volume': buyer['trade_volume'],
                        'trade_date': buyer['trade_date']
                    })

            return popular_stocks
        except Exception as e:
            logger.error(f"获取热门股票失败: {str(e)}")
            return {}

    def get_follow_trades(self):
        """获取跟单记录 - 使用 Profitsyje"""
        try:
            query = """
                SELECT 
                    t.TradeDate AS time,
                    m.MasterName AS master,
                    t.StockCode AS stockcode,
                    t.StockName AS stockname,
                    t.TradeType AS operation,
                    st.SimPrice AS price,
                    st.SimVolume AS volume,
                    st.ProfitLoss AS profit,
                    st.Profitsyje AS balance  -- 使用 Profitsyje
                FROM SimulatedTrades st
                JOIN Trades t ON st.TradeID = t.TradeID
                JOIN Masters m ON t.MasterID = m.MasterID
                ORDER BY st.SimDate DESC
                LIMIT 100
            """
            return self.execute_query(query, fetch=True)
        except Exception as e:
            logger.error(f"获取跟单记录失败: {str(e)}")
            return []

    def get_latest_balance(self):
        """获取最新账户余额 - 使用 Profitsyje"""
        try:
            query = "SELECT Profitsyje FROM SimulatedTrades ORDER BY SimDate DESC LIMIT 1"
            result = self.execute_query(query, fetch=True)
            if result:
                return result[0]['Profitsyje']
            return 1000000.0  # 默认初始资金
        except Exception as e:
            logger.error(f"获取最新账户余额失败: {str(e)}")
            return 1000000.0